
--Total de viviendas de campo
select C.Odei, C.Mes, C.Periodo, C.Conglome
,V.Vivienda
from Campo_Vivienda V 
inner join Campo_Conglomerado C 
	on V.Conglome = C.Conglome	
order by C.Odei, C.Mes, C.Periodo
;

--Sumar nroHogares por Conglomerado
SELECT V.Conglome
,SUM(CONVERT(INT,V.NroHogares)) as nHogaresDeclarados
FROM [Enaho].[dbo].[Campo_Vivienda] V
group by V.Conglome
--having V.Vivienda='015'
;

select C.Odei, C.Mes, C.Periodo, C.Conglome
,V.Vivienda
,VH.nHogaresCampo, VH.h_c, VH.h_i, VH.h_r, VH.h_a, VH.h_d, VH.h_o, VH.h_s
from Campo_Vivienda V 
inner join Campo_Conglomerado C 
	on V.Conglome = C.Conglome	
inner join (select V.Conglome, V.Vivienda
			,COUNT(H.Vivienda) as nHogaresCampo
			,COUNT(CASE WHEN H.ResFin IN (1) THEN 1 ELSE NULL END) AS h_c
			,COUNT(CASE WHEN H.ResFin IN (2) THEN 1 ELSE NULL END) AS h_i
			,COUNT(CASE WHEN H.ResFin IN (3) THEN 1 ELSE NULL END) AS h_r
			,COUNT(CASE WHEN H.ResFin IN (4) THEN 1 ELSE NULL END) AS h_a
			,COUNT(CASE WHEN H.ResFin IN (5) THEN 1 ELSE NULL END) AS h_d
			,COUNT(CASE WHEN H.ResFin IN (7) THEN 1 ELSE NULL END) AS h_o
			,COUNT(CASE WHEN H.ResFin IN (6) THEN 1 ELSE NULL END) AS h_s
			from Campo_Hogar H 
			inner join Campo_Vivienda V 
				on (H.Conglome = V.Conglome and H.Vivienda = V.Vivienda)
			group by V.Conglome, V.Vivienda
			) VH
	on V.Conglome = VH.Conglome and V.Vivienda = VH.Vivienda 				
order by C.Odei, C.Mes, C.Periodo
;